import sqlite3
from Config import configs

dbPath = configs["dbPath"]   # 数据库地址

def InsertFile(UUID,PATH,TIME):
    connection = sqlite3.connect(dbPath)
    connection.execute("PRAGMA busy_timeout = 5000;")  # 防死锁
    cursor = connection.cursor()
    try:
        cursor.execute("INSERT INTO files (uuid, path, time) VALUES ('%s', '%s','%s')" % (UUID, PATH, TIME))
        connection.commit()
    except Exception as e:
        connection.rollback()
        print("寄！",e)
        cursor.close()
        connection.close()
        return e
    return True


def SearchFileByUUID(UUID):
    with sqlite3.connect(dbPath) as connection:
        #   游标
        cursor = connection.cursor()
        try:
            cursor.execute("SELECT * FROM files WHERE uuid = '%s'" % UUID)
        except Exception as e:
            return False,e
        rows = cursor.fetchall()
        if not rows:
            return False,"no such data"
        else:
            return rows[0][1]
        #   返回地址，uuid具有唯一性，应该不会报错罢
        #   这里可能有个雷，fix it!

def SearchArchiveByID(ID):
    with sqlite3.connect(dbPath) as connection:
        #   游标
        cursor = connection.cursor()
        try:
            cursor.execute("SELECT * FROM archives WHERE id = '%s'" % ID)
        except Exception as e:
            return False,e
        rows = cursor.fetchall()
        if not rows:
            return False,"no such data"
        else:
            return rows[0]
        #   返回地址，uuid具有唯一性，应该不会报错罢
        #   这里可能有个雷，fix it!

def SearchArchiveByKeyword(keyword):
    with sqlite3.connect(dbPath) as connection:
        cursor = connection.cursor()

        try:
            cursor.execute("SELECT * FROM archives")
        except Exception as e:
            return False, e
        rows = cursor.fetchall()

        result = []
        for row in rows:
            if keyword in row[2] or keyword in row[3]:
                result.append(row)
        if len(result) > 0:
            return result
        else:
            return False,"没有相关题目"

def InsertArchive(uuid,title,content,comments,category,tags,time):
    connection = sqlite3.connect(dbPath)
    connection.execute("PRAGMA busy_timeout = 5000;")  # 防死锁
    cursor = connection.cursor()
    try:
        cursor.execute("INSERT INTO archives (uuid, title,content,comments,category,tags,unixTime) VALUES ('%s','%s','%s','%s','%s','%s','%s')" % (uuid,title,content,comments,category,tags,time))
        #   可以试试把"archives"的"s"去掉来对Exception进行测试awa
        connection.commit()
    except Exception as e:
        connection.rollback()
        print("寄！", e)
        cursor.close()
        connection.close()
        return e
    cursor.close()
    connection.close()
    return True

# id,Title,Content,Category,Tags,GetTimeNow(True)
# UPDATE table_name
# SET column1 = value1, column2 = value2...., columnN = valueN
# WHERE [condition];
def UpdateArchive(id,title,content,tags,category,unixTime):
     with sqlite3.connect(dbPath) as connection:
        #   游标
        cursor = connection.cursor()
        if category == "":
            try:
                cursor.execute(
                    "UPDATE archives SET title='%s',content='%s',tags='%s',unixTime='%s' WHERE id=%s" % (
                    title, content, tags, unixTime, id))
            except Exception as e:
                cursor.close()
                return False, e
        else:
            try:
                cursor.execute("UPDATE archives SET title='%s',content='%s',tags='%s',category='%s',unixTime='%s' WHERE id=%s" % (title,content,tags,category,unixTime,id))
            except Exception as e:
                cursor.close()
                return False,e
        cursor.close()
        return True

def DeleteDataById(id):
    with sqlite3.connect(dbPath) as connection:
        if SearchArchiveByID(id)[0] == False:
            return False,"no such archive!"
        cursor = connection.cursor()
        try:
            cursor.execute("DELETE FROM archives WHERE id = '%s'" % id)
        except Exception as e:
            return False, e
        return True,"Success!"

def Count():
    with sqlite3.connect(dbPath) as connection:
        #   游标
        cursor = connection.cursor()
        table = 'archives'
        try:
            cursor.execute("SELECT COUNT(*) FROM %s" % table)
        except Exception as e:
            return False,e
        rows = cursor.fetchall()
        if not rows:
            return False,"no such data"
        else:
            return rows